package com.supermap.wzhy.module.fr.service;

import com.supermap.wzhy.data.ConfigHelper;
import com.supermap.wzhy.entity.TMicroIdenmeta;
import com.supermap.wzhy.entity.TMicroTablemeta;
import com.supermap.wzhy.module.data.dao.MicroIdenmetaDao;
import com.supermap.wzhy.module.data.dao.MicroTablemetaDao;
import com.supermap.wzhy.module.fr.dao.FrDao;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 自定义导出xml文件
 * Created by sun'f on 2017-06-28.
 */
public class FrWjgDelDataExpService implements Runnable{

    private final String BASE_TABLE = "YZYM_E_BASEINFO";

    private final String DEL_TABLE = "YZYM_E_BASEINFO_DELETE";

    //文件柜所需工商信息表
    private final  static String[] SUB_TABLES =new String[]{"YZYM_E_BASEINFO","YZYM_E_LEREP","YZYM_E_FINANCE","YZYM_E_INV"
            ,"","YZYM_E_SUB","YZYM_E_ALT","YZYM_E_CANCEL"};

    //表对应XML节点
    private final  static String[] SUB_TABLES_NODES =new String[]{"RECORD","LEREP","INFOFINANCE","INVESTORLIST-INVESTORDOCUMENT"
            ,"","PARENTENTDOCUMENT","INFOALTS-INFOALT","INFOCANCLE"};

    //触发日期时间戳字段
    private String times = "times";
    //企业隶属信息表
    private final String subTable = "YZYM_E_SUB";
    //企业隶属信息临时表
    private final String sub_ls = "yzym_e_subls";

    private int xml_num;
    private int per_size = 1000;
    private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-dd");

    private FrDao frDao ;
    private MicroTablemetaDao microTablemetaDao ;
    private MicroIdenmetaDao microIdenmetaDao ;

    /**
     * 线程构造函数传递参数用
     * @param frDao 工商数据库连接实例
     * @param microTablemetaDao 工商数据表系统数据实例
     * @param microIdenmetaDao  工商数据指标系统实例
     */
    public FrWjgDelDataExpService(FrDao frDao, MicroTablemetaDao microTablemetaDao, MicroIdenmetaDao microIdenmetaDao, int xml_num) {
        this.frDao = frDao;
        this.microTablemetaDao = microTablemetaDao;
        this.microIdenmetaDao = microIdenmetaDao;
        this.xml_num = xml_num;
    }

    @Override
    public void run() {
        String count;
        Date date = new Date();
        String today = simpleDateFormat.format(date);
        String where_sql = " where " + getInformixDateString(times) + " = '"+today+"'";
        String count_sql = "select count(*) from " + DEL_TABLE + where_sql;
        System.out.println(count_sql);
        List<String> count_list = frDao.query("select count(*) from " + DEL_TABLE + where_sql);
        if (count_list.size() > 0){
            count = count_list.get(0).toString();
            System.out.println("导出工商删除数据XML : " + count);
            if(Integer.valueOf(count) > 0){
                exportXml(Integer.valueOf(count),today,xml_num+1);
            }else{
                System.out.println("导出总数为 0 ");
            }
        }else{
            System.out.println("\"select count(*) from " + DEL_TABLE + "\" sql执行异常");
        }

    }

    /**
     * 导出xml函数
     * @param count     导出总数
     * @param today     按日期导出
     * @param xml_num   文件柜已导出数量
     */
    public void exportXml(int count,String today,int xml_num){
        int ao = 0;
        int wjgSum = Integer.valueOf(ConfigHelper.get("wjgsum"));
        String sql = " where  to_char(times,'%Y-%m-%d') = ? " +
                "and  to_char(times,'%Y-%m-%d') = ? ";
        String [] params = new String[2];
        params[0] = today;
        params[1] = today;

        //分支机构子节点名称
        String str = "CHILDENTLIST-CHILDENT";
        //设置CHILDENT 分支机构TMicroIdenmeta 指标对象
        List<TMicroIdenmeta> list = setIdenmeta();
        //分支机构判断处理条件
        String child_sql_sub = "select * from yzym_e_sub where entname = ?";
        String col = ConfigHelper.get("CHILDENTLIST");
        //分支机构节点值
        String child_sql_base2 = "select etpsid,"+col+" from "+sub_ls +" where entname = ?";

        per_size = count<per_size?count:per_size ;
        int num = count/per_size+(count%per_size==0?0:1);
        Map<String ,List<TMicroIdenmeta>> idenMap = new HashMap<>() ;//指标元数据
        //读取全部报表元数据 第一个为基本信息表
        for(String t:SUB_TABLES){
            List<TMicroTablemeta>  tablemetaList  = microTablemetaDao.findByTableName(t);
            if(tablemetaList.size() ==0){
                continue;
            }
            TMicroTablemeta tablemeta  = tablemetaList.get(0) ;
            idenMap.put(t,microIdenmetaDao.findByWJGIden(tablemeta.getMitmid())) ;
        }
        int xmlNum = xml_num ;  //xml数量
        int i = 0;
        //取一定数目数据
        for(i =1 ; i< num+1 ;i++ ){
            int perCount =0;//每个xml的记录数
            int start = (i-1)*per_size ;
            int end = i*per_size ;
            boolean sub_child_boo = false;

            String dataM_sql = "select skip " + start + " first " + per_size + " cast(ETPSID as varchar(50))  ID,"
                    + getQueryFields(idenMap.get(BASE_TABLE)) + ",cast(S_EXT_TIMESTAMP as varchar(50)) " +
                    " S_EXT_TIMESTAMP from " + DEL_TABLE + sql + " order by S_EXT_TIMESTAMP";

            System.out.println(dataM_sql +"\n"+ params[0] +"\n"+ params[1]);

            List<Object[]> dataM  = frDao.query(dataM_sql,params) ;

            Element data = DocumentHelper.createElement("Data");
            boolean  needExp = false ;
            if(dataM==null){  continue; }  //空值判断
            //一次性获取全部报表数据
            Map<String ,Map<String,List<Object[]>>>  subTableDataMap = new HashMap<>() ;

            //假设每次最多只取1000条
            StringBuilder b = new StringBuilder();
            //StringBuilder type = new StringBuilder();
            // String S_EXT_TIMESTAMP = "";
            for(int d= 0; d<dataM.size() ;d++){
                String id =  dataM.get(d)[0].toString() ;//单位唯一标识
                b.append("'"+id+"'") ;
                if(d<dataM.size()-1){
                    b.append(",") ;
                }
            }
            if(dataM.size()>0){
                //查询子报表数据  关联查询太慢
                for(int j=1 ; j< SUB_TABLES.length;j++){
                    if(SUB_TABLES[j]==null||SUB_TABLES[j].equals("")){ continue; }
                    Object[] params1 =new Object[]{params[0],params[1]} ;
                    //跟企业基础信息表关联

                    String subSql = "select  cast(ETPSID as varchar(50))  id," + getQueryFields(idenMap.get(SUB_TABLES[j])) +
                            " from "+SUB_TABLES[j]+" t  where ETPSID in("+b.toString()+")";

                    List<Object[]> dataList = frDao.query(subSql);


                    if (dataList==null) {
                        System.out.println("select  cast(ETPSID as varchar(50))  id," + getQueryFields(idenMap.get(SUB_TABLES[j])) +
                                " from "+SUB_TABLES[j]+" t  where ETPSID in("+b.toString()+")");
                        continue;
                    }
                    System.out.println(SUB_TABLES[j]+" 表 dataList size "+dataList.size());

             
                    subTableDataMap.put(SUB_TABLES[j],listToMapByEtpsid(dataList)) ;

                }
            }

            //逐条遍历数据
            for(int d= 0; d<dataM.size() ;d++){
                sub_child_boo = false;
                int record_index = (i-1)*per_size+(d+1);

                //每个xml包record节点从1开始计数,循环
                if(record_index > wjgSum){
                    record_index = record_index - (int)Math.ceil(record_index/wjgSum)*wjgSum;
                    if(record_index == 0){
                        record_index = wjgSum;
                    }
                }
                //System.out.println(record_index);
                Element record = createRecord(record_index,dataM.get(d),idenMap.get(BASE_TABLE));

                String id =  dataM.get(d)[0].toString() ;//单位唯一标识

                      /*  String uniscidstr =  dataM.get(d)[2].toString() ;//社会信用代码
                        String regnostr =  dataM.get(d)[3].toString() ;//社会信用代码
                        */
                String entnamestr =  dataM.get(d)[4].toString() ;//详细名称

                //String S_EXT_TIMESTAMP1 = dataM.get(d)[22].toString() ;//数据提取时间
                //String types = "";
                       /* if(dataM.get(d)[1] != null){
                            //types = dataM.get(d)[1].toString(); //信息操作类型
                        }*/

                //遍历报表取出子节点数据
                perCount++ ;
                needExp = true ;

                for(int j=1 ; j< SUB_TABLES.length;j++){
                    if(SUB_TABLES[j]==null||SUB_TABLES[j].equals("")){ continue; }
                    if(!subTableDataMap.containsKey(SUB_TABLES[j])){ continue;}
                    List<Object[]> dataList = new ArrayList<>();

                    dataList =  subTableDataMap.get(SUB_TABLES[j]).get(id);

                    if(dataList == null||dataList.size() ==0){ continue; }

                    Element subTNode = createSubNode(SUB_TABLES_NODES[j],dataList,idenMap.get(SUB_TABLES[j])) ;
                    record.add(subTNode);


                    //同一子节点并列情况
                    if(SUB_TABLES[j].equals(subTable)){
                        //String child_sql_sub = "select * from yzym_e_sub where entname = ? and regno = ? or uniscid = ?";
                        //String child_sql_sub = "select * from yzym_e_sub where entname = ?";
                        List li = frDao.query(child_sql_sub,entnamestr);
                        if(li != null){
                            if(li.size() > 0){
                                sub_child_boo = true;
                            }
                        }
                        //sub_child_boo = sub(id);
                        //判断是否存在分支机构节点的情况
                        if(sub_child_boo){
                            Map<String,List<TMicroIdenmeta>> map = new HashMap();
                            map.put(subTable,list);
                            List sub_d = frDao.query(child_sql_base2,entnamestr);
                            Element subTNode_child = createSubNode(str,sub_d,map.get(SUB_TABLES[j]));
                            record.add(subTNode_child);
                            System.out.println("第 "+ao+" 条数据有分支机构信息!");
                            //sub_child_boo = false;
                        }
                    }
                }
                /** 必须需要法人信息 **/
                createLerepInfo(record);

                //修改所有文本的信息操作类型为‘1’
                record = setDocument(record,"1");

                ao++;
                data.add(record);

                //xmlNum = getXmlNum(xmlNum);//每天零点置零

                //每wjgSum条输出文件
                if((d+1)%wjgSum == 0){
                    expOneXmlFile(xmlNum++,perCount,data,today,sub_child_boo);
                    needExp =false ;
                    perCount =0 ;
                    data = null ;
                    data = DocumentHelper.createElement("Data");
                }
            }
            System.out.println("当前已导出 " + ao + " 数据!");

            //剩余部分再导出
            if(needExp){
                //System.out.println("当前已导出 "+ao+" 数据!");
                expOneXmlFile(xmlNum++,perCount,data,today,sub_child_boo);
                needExp =false ;
                perCount = 0;
                System.out.println("当前导出 " + i*per_size);
            }
        }

    }


    //创建法人信息节点
    private Element createLerepInfo(Element record){
        List<Element> list = record.elements();
        boolean boo = false;
        for(Element ele:list){
            if("LEREP".equals(ele.getName())){
                boo = true;
            }
        }

        if(boo == false){
            Element unisid = record.element("UNISCID");
            String id = unisid.getText();
            Element lerep = DocumentHelper.createElement("LEREP");
            Element lerep_child = DocumentHelper.createElement("LEREP");
            lerep_child.addText(insertLerepText(id));
            lerep.add(lerep_child);

            record.add(lerep);
        }
        return  record;
    }

    private String insertLerepText(String unisid){
        String sql = "select lerep from "+BASE_TABLE+" where UNISCID = ?";
        List list = frDao.query(sql,unisid);
        if(list.size() > 0){
            return list.get(0)==null?"":list.get(0).toString();
        }else{
            return "";
        }

    }

    /**
     * 导出xml文件
     * @param data
     * @return
     */
    private boolean expOneXmlFile(int xmlNum ,int perCount,Element data,String date,boolean coo){
        String number = String.format("%06d", xmlNum);
        String endTime = date.replaceAll("-","");
        String dataPackNum =  "130000" +endTime+number ;   //数据包编号
        //根节点
        Element root = DocumentHelper.createElement("Package");
        Element head = createHead(dataPackNum,perCount) ;//数据头部描述信息
        root.add(head);
        root.add(data);
        //输出文件
        createXmlFile(xmlNum,root,endTime,coo);
        //System.out.println();
        return true ;
    }


    /**
     * 创建单位数据基本信息节点
     * @param index
     * @param data
     * @param idenmetas
     * @return
     */
    private  Element createRecord(int index ,Object[] data,List<TMicroIdenmeta> idenmetas){

        Element record = DocumentHelper.createElement("Record") ;
        record.addAttribute("index",(index)+"") ;
        int len = idenmetas.size();
        for(int i =0 ; i< len ;i++){
            int type = idenmetas.get(i).getIdenType() ;
            String value = "";
            //格式化日期类型
            if(type == 3){
                try {
                    if(data[i+1] != null){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //yyyy-MM-dd HH:mm:ss
                        SimpleDateFormat formats = new SimpleDateFormat("yyyy-MM-dd"); //yyyy-MM-dd
                        boolean contains_etp = data[i+1].toString().contains(":");
                        if(contains_etp){
                            if(data[i+1] != null){
                                String de = data[i+1].toString();
                                if(de.contains(".")){
                                    de = de.substring(0, de.indexOf("."));
                                }
                                value = sdf.format(sdf.parse(de)) ;
                            }else {
                                value = "";
                                //System.out.println();
                            }
                        }else{
                            if(data[i+1] != null){
                                String de = data[i+1].toString();
                                if(de.contains(".")){
                                    de = de.substring(0, de.indexOf("."));
                                }
                                value = formats.format(formats.parse(de)) ;
                                //System.out.println();
                            }else {
                                value = "";
                            }
                        }
                    }else{
                        value = "";
                    }

                }   catch (Exception e) {
                    System.out.println("日期类型转换出错 ：" + data[i+1]);
                    value ="";
                }
            }else{
                value = data[i+1]!=null?data[i+1].toString():"" ;

                value = speacial(value);

            }
            // 判断文件柜字段是否有别名
            if(idenmetas.get(i).getWjgName() == null){
                record.addElement(idenmetas.get(i).getIdenCode().toUpperCase()).addText(value) ;
            }else{
                record.addElement(idenmetas.get(i).getWjgName().toUpperCase()).addText(value) ;
            }

        }

        return  record ;
    }



    /**
     * 获取子报表的节点
     * @param nodeName
     * @param dataList
     * @param idenmetas
     * @return
     */
    private  Element createSubNode(String nodeName ,List<Object[]>dataList,List<TMicroIdenmeta> idenmetas){
        String[] nodes = nodeName.split("-") ;//多个则要考虑多个子节点
        Element rootNode = DocumentHelper.createElement(nodes[0]) ;
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy-MM-dd");
        int len = idenmetas.size();
        for(Object[] data:dataList){
            Element subNode = nodes.length>1?DocumentHelper.createElement(nodes[1]):null ;
            for(int i =0 ; i< len ;i++){
                int type = idenmetas.get(i).getIdenType() ;
                //int dsize = dataList.size() ;
                String value = "";
                //格式化日期类型
                if(type == 3){
                    try {
                        if(data[i+1] != null){
                            String de = data[i+1].toString();
                            if(de.contains(".")){
                                de = de.substring(0, de.indexOf("."));
                                value = formatter.format(formatter.parse(de)) ;
                            }else{
                                value = formatter2.format(formatter2.parse(de)) ;
                            }
                        }else {
                            value = "";
                        }

                        // value = formatter.format(data[i+1]) ;
                    }   catch (Exception e) {
                        value ="";
                    }
                }else{
                    value = data[i+1]!=null?data[i+1].toString():"" ;

                    value = speacial(value);
                }
                if(subNode == null){
                    if(idenmetas.get(i).getWjgName() != null){
                        rootNode.addElement(idenmetas.get(i).getWjgName().toUpperCase()).addText(value) ;
                    }else{
                        rootNode.addElement(idenmetas.get(i).getIdenCode().toUpperCase()).addText(value) ;
                    }
                }else{
                    if(idenmetas.get(i).getWjgName() != null){
                        subNode.addElement(idenmetas.get(i).getWjgName().toUpperCase()).addText(value) ;
                    }else{
                        subNode.addElement(idenmetas.get(i).getIdenCode().toUpperCase()).addText(value) ;
                    }
                }
            }
            if(subNode != null){
                rootNode.add(subNode) ;
            }
        }
        return  rootNode ;
    }


    /**
     * 输出xml文件到指定目录
     * @param root
     * @return
     */
    private  boolean createXmlFile(int xmlnum , Element root, String date, boolean coo){
        //设置文件编码
        OutputFormat xmlFormat = new OutputFormat();
        xmlFormat.setEncoding("UTF-8");

        // 设置换行
        xmlFormat.setNewlines(true);
        // 生成缩进
        xmlFormat.setIndent(true);
        xmlFormat.setNewLineAfterDeclaration(false);
        // 使用4个空格进行缩进, 可以兼容文本编辑器
        xmlFormat.setIndent("    ");
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
        //SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//        第1到6位为省级行政区划代码；
//        第7到14位为数据交换的日期，表示方法为：YYYYMMDD；
//        第15到20位为顺序号，每天零点置零，以000001为始。
//        举例：WZHY_GSXX_42000020161025000001.xml
        String number = String.format("%06d", xmlnum);
        //文件名包含的日期不能含有'-'
        //date = date.replaceAll("-", "");

        String base =  ConfigHelper.get("wjgpath")+"/"+date +"/"+"tjdata"+"/"+"jbxx"+"/";

        String fileName = "WZHY_GSXX_130000"+ date +number +".xml";

        if(coo){
            System.out.println(fileName);
        }

        String path = base+fileName ;
        File f = new File(base) ;
        if(!f.exists()){ f.mkdirs() ;}
        //if(new File(path).exists()){ new File(path).delete() ; }
        try {
            Document document = DocumentHelper.createDocument();
            document.add(root);
            document.setXMLEncoding("UTF-8");

            //创建写文件方法
            FileOutputStream fos = new FileOutputStream(path);
            XMLWriter xmlWriter = new XMLWriter(fos,xmlFormat);

            //写入文件
            xmlWriter.write(document);
            //System.out.println("Encoding :　"+document.getXMLEncoding());
            //关闭
            xmlWriter.close();
            return  true ;
        }   catch (Exception e){
            e.printStackTrace();
        }
        return  false ;

    }

    /**
     * 构造表头
     * @return
     */
    private Element createHead(String dataPackNum,int perCount){

        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddhhmmss");
        Element root = DocumentHelper.createElement("PackageHead") ;
        root.addElement("SJBBH").addText(dataPackNum);   // 数据包编号
        root.addElement("SJBLX").addText("GSXX") ;  //数据包类型
        root.addElement("DWDM").addText("130000") ;    //单位代码
        root.addElement("DWMC").addText("河北省工商行政管理局") ;    //单位名称
        root.addElement("JLS").addText(perCount+"") ;     //记录数
        root.addElement("INFODATE").addText(formatter.format(new Date())) ;  //数据包生成时间，格式为yyyyMMddhhmmss
        return  root ;
    }


    /**
     * 获取查询指标字符串
     * @param idenmetas
     * @return
     */
    private String  getQueryFields(List<TMicroIdenmeta> idenmetas){
        StringBuilder stringBuilder = new StringBuilder();
        int index =0 ;
        int size = idenmetas.size() ;

        for(TMicroIdenmeta i:idenmetas){
            //informix char类型转换
            if(i.getIdenType() == 1&&i.getIdenLength()<=255){
                stringBuilder.append("cast("+i.getIdenCode().toUpperCase()+ " as varchar(255)) "+i.getIdenCode().toUpperCase());
            }else if(i.getIdenType() == 1&&i.getIdenLength()==3000){
                //lvarchar : informix独有的长字符类型
                stringBuilder.append("cast("+i.getIdenCode().toUpperCase()+ " as lvarchar(3000)) "+i.getIdenCode().toUpperCase());
            }
            else{
                stringBuilder.append(i.getIdenCode().toUpperCase());
            }
            if(index < size-1){
                stringBuilder.append(",");
            }
            index++ ;
        }
        return stringBuilder.toString();
    }


    /**
     * 将datalist转换成map对象
     * @param datalist
     * @return
     */
    private Map<String , List<Object[]>> listToMapByEtpsid(List<Object[]> datalist){
        Map<String , List<Object[]>> map = new HashMap<>() ;
        for(Object[] o:datalist){
            String id =  o[0].toString() ;//单位唯一标识
            if(!map.containsKey(id)){
                map.put(id,new ArrayList<Object[]>());
            }
            map.get(id).add(o);
        }
        return  map ;
    }


    /**
     * 设置分支机构子节点指标
     * @return
     */
    private List<TMicroIdenmeta> setIdenmeta(){
        List<TMicroIdenmeta> li = new ArrayList<>();
        String sub_child = ConfigHelper.get("CHILDENTLIST");
        String [] child = sub_child.split(",");
        long l = 1;
        BigDecimal b = BigDecimal.valueOf(l);
        for(int i = 0;i<child.length;i++){
            TMicroIdenmeta iden = new TMicroIdenmeta();
            iden.setIdenCode(child[i]);
            iden.setIsfjg(b);
            iden.setIdenType(1);
            if("BRUNISCID".equals(child[i])){
                iden.setWjgName("UNISCID");
            }
            if("BRREGNO".equals(child[i])){
                iden.setWjgName("REGNO");
            }
            if("ENTNAME".equals(child[i])){
                iden.setWjgName("BRNAME");
            }
            if("LEREP".equals(child[i])){
                iden.setWjgName("PRIL");
            }
            if("PROLOC".equals(child[i])){
                iden.setWjgName("OPLOC");
            }

            if("ESTDATE".equals(child[i]) || "OPFROM".equals(child[i])
                    || "OPTO".equals(child[i]) || "APPRDATE".equals(child[i])){
                iden.setIdenType(3);
            }
            li.add(i,iden);
        }
        return li;
    }


    /**
     * 根据信息操作类型修改XML文档的某个节点
     * @param record 文档根节点
     * @param type 要修改的信息操作类型
     * @return
     */
    private Element setDocument(Element record,String type){
        List<Element> li = record.selectNodes("INFOACTIONTYPE");
        if(li.size() > 0){
            Element element = li.get(0);
            element.setText(type);
        }
        return record;
    }


    /**
     * 节点文本乱码转换
     * @param txt
     * @return
     */
    private String speacial(String txt){
        String res = "";
        for(int i = 0; i < txt.length(); ++i){
            char ch = txt.charAt(i);
            if( Character.isDefined(ch) && ch!= '&' && ch != '<' && ch != '>'
                    //转换字符是否需要符合高低代理项代码单元?
                    && !Character.isHighSurrogate(ch) && !Character.isISOControl(ch) &&
                    !Character.isLowSurrogate(ch)
                    ){
                res = res + ch;
            }else{
                ch = ' ';
                res += ch;
            }
        }
        return res;
    }

    private String getInformixDateString(String col){
        return "to_char("+col+",'%Y-%m-%d')";
    }
}
